/**
 * 
 */
package cn.com.gxetc.assistant.service.impl;

import java.sql.ResultSet;
import java.sql.SQLException;
import java.util.List;

import javax.sql.DataSource;

import org.springframework.beans.factory.annotation.Autowired;
import org.springframework.jdbc.core.JdbcTemplate;
import org.springframework.jdbc.core.RowMapper;
import org.springframework.stereotype.Service;

import cn.com.gxetc.assistant.dto.Todo;
import cn.com.gxetc.assistant.dto.TodoCount;
import cn.com.gxetc.assistant.dto.TodoPublish;
import cn.com.gxetc.assistant.service.TodoService;

/**
 * @author yfm
 *
 */
@Service
public class TodoServiceImpl implements TodoService{
	
	
	private JdbcTemplate jdbcTemplate;
	
	//@Qualifier("oadb")
    @Autowired
    public void setDataSource(DataSource dataSource) {
        this.jdbcTemplate = new JdbcTemplate(dataSource);
    }

	
   
	public List<Todo> findByEmployeeId(String employeeId) {
		
		return jdbcTemplate.query("select App_ID,AppT_ID,AppO_Title,Reg_Code,App_State,Del_Flag,Sub_Time,Sub_Code FROM [C6].[dbo].[JHOA_Approve] where (App_State=1 or App_State=2) and Del_Flag=0 and Reg_Code=? order by Sub_Time asc",new String[]{employeeId}, new TodoMapper());
	
	}
	
	public List<TodoCount> countByEmployeeIds(String ids){
		return jdbcTemplate.query("select Reg_Code,count(*) as todoNum FROM [C6].[dbo].[JHOA_Approve] where (App_State=1 or App_State=2) and Del_Flag=0 and Reg_Code in(?) group by Reg_Code",new String[]{ids},new TodoCountMapper());
	}
	
	public List<TodoCount> countByEmployeeIds(){
		return jdbcTemplate.query("select Reg_Code,count(*) as todoNum FROM [C6].[dbo].[JHOA_Approve] where (App_State=1 or App_State=2) and Del_Flag=0  GROUP BY Reg_Code",new TodoCountMapper());
	}
	
	private static final class TodoMapper implements RowMapper<Todo> {

	    public Todo mapRow(ResultSet rs, int rowNum) throws SQLException {
	    	
	    	Todo todo = new Todo();
	    	todo.setId(rs.getString("App_ID"));
	    	todo.setDutyUser(rs.getString("Reg_Code"));
	    	todo.setStartTime(rs.getTimestamp("Sub_Time"));
	    	todo.setState(rs.getString("App_State"));
	        todo.setTitle(rs.getString("AppO_Title"));
	        todo.setFromUser(rs.getString("Sub_Code"));
	        return todo;
	    }
	}
	private static final class TodoCountMapper implements RowMapper<TodoCount> {

	    public TodoCount mapRow(ResultSet rs, int rowNum) throws SQLException {
	    	
	    	TodoCount count = new TodoCount();
	    	count.setUserId(rs.getString("Reg_Code"));
	    	count.setCount(rs.getLong("todoNum"));
	        return count;
	    }
	}
	

	public List<Todo> findTodus() {
		StringBuilder sql=new StringBuilder("SELECT DATEDIFF(mi,Sub_Time,GETDATE()) as Timeout,ja.Reg_Code,ja.Sub_Code,au.cUserName,au.cUserCom,App_State,Sub_Time,tt.Name as Tem_TypeName,tm.TEM_TypeID,tm.TEM_Name FROM ");
		sql.append(" JHOA_Approve ja LEFT JOIN AIP_USER au on ja.Reg_Code=au.cUserID LEFT JOIN JHOA_APPROVE_Template tm on ja.AppT_ID=tm.TEM_ID LEFT JOIN JHOA_APPROVE_Temp_Type tt on tm.TEM_TypeID=tt.ID");
		sql.append(" where (App_State=1 or App_State=2) and Del_Flag=0");
		//开始时间
		//query.append(" and Sub_Time > ?");
		sql.append(" ORDER by Sub_Time desc");
		
		return jdbcTemplate.query(sql.toString(), new TodoMapper());
	}
	
	public List<TodoPublish> findPublish(Long minis) {
		StringBuilder sql=new StringBuilder("SELECT Count(ja.App_ID) as overCount,AVG(DATEDIFF(mi,Sub_Time,GETDATE())) as timeOut,ja.Reg_Code,au.cUserName,au.cUserCom FROM ");
		sql.append(" JHOA_Approve ja LEFT JOIN AIP_USER au on ja.Reg_Code=au.cUserID ")
		   .append(" WHERE (App_State=1 or App_State=2) ")
		   .append(" and Del_Flag=0 and au.cUserCom !='停用账号组'")
		   .append(" and DATEDIFF(mi,Sub_Time,GETDATE())>?")
		   .append(" and ja.Reg_Code!='xitgly'")
		   .append(" and (AppT_ID !='85daddc8f9df4bd68eb204d70f1d46f0')")
		   .append(" GROUP BY ja.Reg_Code,ja.Reg_Code,au.cUserName,au.cUserCom");
		
		return jdbcTemplate.query(sql.toString(), new Long[]{minis},new TodoPubishMapper());
	}
	
	private static final class TodoPubishMapper implements RowMapper<TodoPublish> {

	    public TodoPublish mapRow(ResultSet rs, int rowNum) throws SQLException {
	    	
	    	TodoPublish count = new TodoPublish();
	    	count.setCount(rs.getInt("overCount"));
	    	count.setName(rs.getString("cUserName"));
	    	count.setDept(rs.getString("cUserCom"));
	    	count.setTimeout(rs.getLong("timeOut"));
	        return count;
	    }
	}

}
